package cn.sfa.service.messagemanager;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import org.hsqldb.Types;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import cn.sfa.common.pagination.Pagination;
import cn.sfa.common.utils.CommonUtil;
import cn.sfa.entity.message.MessageEntity;

@Service
public class MessageManagerService {
	Logger log = LoggerFactory.getLogger(MessageManagerService.class);

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;
 

	/**
	 * 查询列表
	 * 
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param Id
	 * @param BaseType
	 * @return
	 */
	public Pagination GetMessageBaseList(int currentPage, int numPerPage,
			String url, String AgentId) {
		String sql = "SELECT * FROM ( select A.SALEMESSAGEBASEID,A.MESSAGENAME,A.MESSAGETITLE,A.MESSAGEPICTUREURL,A.masterplate,"
				+ "(SELECT COUNT(1) FROM  salemessagebase_agent B WHERE B.SALEMESSAGEBASEID=A.SALEMESSAGEBASEID  AND B.SUBSCRIBE='Y' "
				+ "AND B.AGENTID='"
				+ AgentId
				+ "') ISTURE, " 
				+ "(SELECT COUNT(1) FROM  salemessagebase_agent B WHERE B.SALEMESSAGEBASEID=A.SALEMESSAGEBASEID  AND B.SUBSCRIPTION = 'Y' " 
				+ "AND B.AGENTID='"
				+ AgentId
				+ "'	) ISSUBSCRIPTION  from salemessagebase A) ";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("AgentId", CommonUtil.jugeAndFixNull(AgentId));
		return new Pagination(sql, paramMap, currentPage, numPerPage, url,
				jdbcTemplate);
	}

	/**
	 * 根据ID查询，返回enty
	 * 
	 * @param currentPage
	 * @param numPerPage
	 * @param url
	 * @param Id
	 * @return
	 */
	public MessageEntity GetMessageBase(String Id) {
		String sql = " select * from salemessagebase a where 1=1 ";
		if (!CommonUtil.isEmpty(Id)) {
			sql += "and a.salemessagebaseid=:salemessagebaseid";
		}
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("salemessagebaseid", CommonUtil.jugeAndFixNull(Id));
		return jdbcTemplate.queryForObject(sql, paramMap,
				new BeanPropertyRowMapper<MessageEntity>(MessageEntity.class));
	}

	/**
	 * 订阅
	 * 
	 * @param SaleMessageBaseId
	 * @return
	 */
	public String EditMessageBaseAgentById(final String AgentId,
			final String BaseId, final String Subscribe,final String Type) {
		String returnData = "";
		String sql = "BEGIN pkg_sfa_customer.messagesubscribe(:AgentId,:BaseId,:Subscribe,:returnData,:Type); END;";
		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("AgentId", AgentId);
		paramMap.put("BaseId", BaseId);
		paramMap.put("Subscribe", Subscribe);
		paramMap.put("returnData", returnData);
		paramMap.put("Type", Type);

		returnData = jdbcTemplate.getJdbcOperations().execute(sql,
				new CallableStatementCallback<String>() {
					@Override
					public String doInCallableStatement(CallableStatement cs)
							throws SQLException, DataAccessException {
						cs.setString(1, AgentId);
						cs.setString(2, BaseId);
						cs.setString(3, Subscribe);
						cs.setString(4, Type);
						cs.registerOutParameter(5, Types.VARCHAR);
						cs.execute();
						return cs.getString(5);
					}
				});
		return returnData;
	}

}
